#include "localdb.h"
#include <QSqlQuery>
#include <QSqlError>

static const QString create_shop =
    R"(CREATE TABLE IF NOT EXISTS ShopTable(
        shopCode TEXT constraint ShopTable_pk primary key,
        tenantSign TEXT, shopName TEXT, printerDpi TEXT,
        printerName TEXT, printerIp TEXT, printerPort TEXT,
        vShift INTEGER, hShift TEXT, printerXMl TEXT,
        printerTemplateVersion TEXT, token TEXT);)";

static const QString read_shop = R"(SELECT tenantSign, shopCode, shopName,
                                    printerDpi, printerName, printerIp,
                                    printerPort, printerXML, vShift,
                                    hShift, printerTemplateVersion from ShopTable limit 1;)";

static const QString create_template =
    "CREATE TABLE  IF NOT EXISTS  TemplateTable( \
    id INTEGER, name TEXT, shortName TEXT, \
    unit INTEGER, saveType INTEGER, operationType INTEGER, dateUnit INTEGER, dateCount INTEGER, hasWarn INTEGER, \
    warnCount INTEGER, warnUnit INTEGER, hasPart INTEGER, partCount INTEGER, partUnit INTEGER, remark TEXT, \
    createTime TEXT, typeName TEXT);";

static const QString create_record =
    R"(CREATE TABLE IF NOT EXISTS RecordTable(
    localId TEXT PRIMARY KEY,
    shopCode TEXT, pId TEXT, makeAt INTEGER,
    vailAt INTEGER, status INTEGER, templateId INTEGER,
    name TEXT, shortName TEXT, unit INTEGER,
    saveType INTEGER, operationType INTEGER, dateUnit INTEGER,
    dateCount INTEGER, hasWarn INTEGER, warnCount INTEGER,
    warnUnit INTEGER, hasPart INTEGER, partCount INTEGER,
    partUnit INTEGER, remark TEXT, createTime TEXT,
    typeName TEXT, deleted INTEGER);)";

static const QString read_record = R"(SELECT
                                localId, shopCode, pId, makeAt,
                                templateId, name, shortName,
                                unit, saveType, operationType,
                                dateUnit, dateCount, hasWarn,
                                warnCount, warnUnit, hasPart,
                                partCount, partUnit, remark,
                                createTime, typeName,  vailAt,
                                deleted from RecordTable where shopCode=:shopCode;)";
static const QString del_record = R"(DELETE FROM RecordTable where shopCode=:shopCode and localId=:localId;)";

LocalDb::LocalDb(QObject *parent)
    : QObject{parent}
{}


bool LocalDb::Open(QString path){
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(path);
    if(!db.open()){
        return false;
    }

    QSqlQuery query;
    if (!query.exec(create_shop)) {
        qDebug() << "创建门店信息表失败：" << query.lastError().text();
        return false;
    }

    if (!query.exec(create_template)) {
        qDebug() << "创建模板表失败：" << query.lastError().text();
        return false;
    }

    if (!query.exec(create_record)) {
        qDebug() << "创建记录表失败：" << query.lastError().text();
        return false;
    }

    qDebug() << "打开数据库成功!";
    return true;
}

QJsonObject LocalDb::ReadShopInfo(){
    QSqlQuery query;
    QJsonObject info;

    if (!query.exec(read_shop)) {
        qDebug() << "读取门店信息失败。" << query.lastError().text();
        return info;
    }

    if (query.next()) {
        info["tenantSign"] = query.value("tenantSign").toString();
        info["shopCode"] = query.value("shopCode").toString();
        info["shopName"] = query.value("shopName").toString();
        info["printerDpi"] = query.value("printerDpi").toString();
        info["printerName"] = query.value("printerName").toString();
        info["printerIp"] = query.value("printerIp").toString();
        info["printerPort"] = query.value("printerPort").toString();
        info["vShift"] = query.value("vShift").toString();
        info["hShift"] = query.value("hShift").toString();
        info["printerXML"] = query.value("printerXML").toString();
        info["printerTemplateVersion"] = query.value("printerTemplateVersion").toString();
    }
    return info;
}

bool shopInfoExists(int id) {
    QSqlQuery query;
    query.prepare("SELECT COUNT(id) FROM ShopTable WHERE id = :id");
    query.bindValue(":id", id);
    if (query.exec() && query.next()) {
        return query.value(0).toInt() > 0;
    }
    return false;
}

// 生成 SET key=excluded.key 子句
QString LocalDb::generateUpdateClause(const QStringList &fields) {
    QStringList updates;
    for (const QString &field : fields) {
        if (field != "id") {  // 主键不更新
            updates << QString("%1 = excluded.%1").arg(field);
        }
    }
    return updates.join(", ");
}

bool LocalDb::SaveShopInfo(QJsonObject data){
    QSqlQuery query;
    QStringList fields;
    QStringList bindValues;

    if (!query.exec("DELETE FROM ShopTable")) {
        qDebug() << "清除门店数据失败。" << query.lastError().text();
    }

    query.clear();
    // 动态构建字段和绑定占位符
    for (const QString &key : data.keys()) {
        fields << key;
        bindValues << ":" + key;
    }

    // 构造 UPSERT 语句
    QString sql = QString(
                      "INSERT INTO ShopTable (%1) VALUES (%2) "
                      ).arg(fields.join(", "),
                           bindValues.join(", "));

    query.prepare(sql);

    // 绑定所有参数
    for (const QString &key : data.keys()) {
        QString value = data.value(key).toString();
        query.bindValue(":" + key, value);
    }

    // qDebug() << "Final SQL:" << query.lastQuery();
    // qDebug() << "Bound values:" << query.boundValues();

    if (!query.exec()) {
        qDebug() << "保存失败：" << query.lastError().text();
        return false;
    }
    return true;
}

const QString insertRecordSql = R"(REPLACE INTO RecordTable
                                (localId, shopCode, pId,
                                makeAt, vailAt, status,
                                templateId, name, shortName,
                                unit, saveType, operationType,
                                dateUnit, dateCount, hasWarn,
                                warnCount, warnUnit, hasPart,
                                partCount, partUnit, remark,
                                createTime, typeName, deleted
                                ) VALUES (
                                :localId, :shopCode, :pId,
                                :makeAt, :vailAt, :status,
                                :templateId, :name, :shortName,
                                :unit, :saveType, :operationType,
                                :dateUnit, :dateCount, :hasWarn,
                                :warnCount, :warnUnit, :hasPart,
                                :partCount, :partUnit, :remark,
                                :createTime, :typeName, :deleted
                                );)";

void bindInt(QSqlQuery* query, QJsonObject obj, QString key) {
    int value = obj.value(key).toInt();
    query->bindValue(":" + key, value);
}

void bindString(QSqlQuery* query, QJsonObject obj, QString key) {
    QString value = obj.value(key).toString();
    query->bindValue(":" + key, value);
}

bool LocalDb::SaveRecords(QJsonArray list){
    QSqlQuery query;

    for (auto record: list) {
        query.clear();
        query.prepare(insertRecordSql);
        QJsonObject data = record.toObject();

        bindString(&query, data, "localId");
        bindString(&query, data, "createTime");
        bindString(&query, data, "name");
        bindString(&query, data, "remark");
        bindString(&query, data, "pId");
        bindString(&query, data, "shopCode");
        bindString(&query, data, "shortName");
        bindString(&query, data, "typeName");
        bindInt(&query, data, "dateCount");
        bindInt(&query, data, "dateUnit");
        bindInt(&query, data, "deleted");
        bindInt(&query, data, "hasPart");
        bindInt(&query, data, "hasWarn");
        bindInt(&query, data, "makeAt");
        bindInt(&query, data, "operationType");
        bindInt(&query, data, "partCount");
        bindInt(&query, data, "partUnit");
        bindInt(&query, data, "saveType");
        bindInt(&query, data, "stauts");
        bindInt(&query, data, "templateId");
        bindInt(&query, data, "unit");
        bindInt(&query, data, "vailAt");
        bindInt(&query, data, "warnCount");
        bindInt(&query, data, "warnUnit");

        // qDebug() << "Final SQL:" << query.lastQuery();
        // qDebug() << "Bound values:" << query.boundValues();

        if (!query.exec()) {
            qDebug() << "保存记录信息失败：" << query.lastError().text();
            return false;
        }
        qDebug() << "保存记录信息成功。";
    }
    return true;
}

bool LocalDb::RemoveRecords(QJsonArray list) {
    QSqlQuery query;

    for (auto item:list) {
        query.clear();
        QJsonObject record = item.toObject();
        QString localId = record["localId"].toString();
        QString shopCode = record["shopCode"].toString();
        query.prepare(del_record);
        query.bindValue(":shopCode", shopCode);
        query.bindValue(":localId", localId);
        query.exec();
    }

    return true;
}

QJsonArray LocalDb::ReadRecords(QString shopCode){
    QSqlQuery query;
    QJsonArray list;

    query.prepare(read_record);
    query.bindValue(":shopCode", shopCode);

    if (!query.exec()) {
        qDebug() << "读取记录信息失败。" << query.lastError().text();
        return list;
    }


    while (query.next()) {
        QJsonObject obj;
        QString localId = query.value("localId").toString();
        obj["localId"] = localId;
        obj["shopCode"] = query.value("shopCode").toString();
        // obj["recordId"] = query.value("recordId").toInt();
        obj["pId"] = query.value("pId").toString();
        obj["makeAt"] = query.value("makeAt").toInt();
        obj["vailAt"] = query.value("vailAt").toInt();
        obj["templateId"] = query.value("templateId").toInt();
        obj["name"] = query.value("name").toString();
        obj["shortName"] = query.value("shortName").toString();
        obj["unit"] = query.value("unit").toInt();
        obj["saveType"] = query.value("saveType").toInt();
        obj["operationType"] = query.value("operationType").toInt();
        obj["dateUnit"] = query.value("dateUnit").toInt();
        obj["dateCount"] = query.value("dateCount").toInt();
        obj["hasWarn"] = query.value("hasWarn").toInt();
        obj["warnCount"] = query.value("warnCount").toInt();
        obj["warnUnit"] = query.value("warnUnit").toInt();
        obj["hasPart"] = query.value("hasPart").toInt();
        obj["partCount"] = query.value("partCount").toInt();
        obj["partUnit"] = query.value("partUnit").toInt();
        obj["remark"] = query.value("remark").toString();
        obj["createTime"] = query.value("createTime").toString();
        obj["typeName"] = query.value("typeName").toString();
        obj["deleted"] = query.value("deleted").toInt();
        list.append(obj);
    }
    return list;
}
